﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    public class ItemOfficeCount
    {
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataSet GetAll(string itemClsNo, string startDate, string endDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT e.*, FItemClsName,FEntityNo FROM (select a.FItemClsNo,a.FItemNo,a.FItemName,ISNULL(B.amount,0) AS amount1,");
            strSql.Append(" ISNULL(B.qty,0) AS qty1,ISNULL(c.amount,0) AS amount2,ISNULL(c.qty,0) AS qty2,ISNULL(FApplyQty,0) AS FApplyQty,a.FItemSpec from dbo.t_Item AS A");
            strSql.Append(" LEFT JOIN ");
            strSql.Append(" (select c.*,SUM(d.FAmount) AS amount,SUM(d.FQty) AS qty");
            strSql.Append(" from(select a.FItemClsNo,a.FItemClsName,b.FEntityNo,b.FItemName,b.FItemNo,b.FItemSpec");
            strSql.Append(" from dbo.t_ItemCls AS a left join dbo.t_Item AS b");
            strSql.Append(" on a.FItemClsNo=b.FItemClsNo) AS c inner join ");
            strSql.Append(" (select FQty,FAmount,FItemNo,FInsDate from dbo.t_ItemPurchaseEntry inner join ");
            strSql.Append(" dbo.t_ItemPurchase ");
            strSql.Append(" on dbo.t_ItemPurchase.FItemPurchaseID =dbo.t_ItemPurchaseEntry.FItemPurchaseID");

            if (startDate != "" )
            {
                strSql.Append(" where DATEDIFF(DAY,FInsDate," + startDate + ")<=0 ");               
            }
            if (endDate != "") 
            {
                strSql.Append(" and DATEDIFF(DAY,FInsDate," + endDate + ")>=0 ");
            }
            strSql.Append(" ) AS d ON d.FItemNo=c.FItemNo");
            strSql.Append(" group by c.FEntityNo,c.FItemClsName,c.FItemClsNo,c.FItemName,c.FItemNo,c.FItemSpec) AS B");
            strSql.Append(" ON A.FItemNo=B.FItemNo");
            strSql.Append(" left join");
            strSql.Append(" (select c.*,SUM(d.FAmount) AS amount,SUM(d.FQty)AS qty ,SUM(CAST(ISNULL(d.FApplyQty,0) as int)) AS FApplyQty");
            strSql.Append(" from(select a.FItemClsNo,a.FItemClsName,b.FEntityNo,b.FItemName,b.FItemNo,b.FItemSpec");
            strSql.Append(" from dbo.t_ItemCls AS a left join dbo.t_Item AS b");
            strSql.Append(" on a.FItemClsNo=b.FItemClsNo) AS c inner join ");
            strSql.Append(" (select FApplyQty,FQty,FAmount,FItemNo,FAuditDate from dbo.t_ItemRequisitionApplyEntry inner join dbo.t_ItemRequisitionApply");
            strSql.Append(" on dbo.t_ItemRequisitionApplyEntry.FItemRequisitionApplyID =dbo.t_ItemRequisitionApply .FItemRequisitionApplyID");
            if (startDate != "")
            {
                strSql.Append(" where DATEDIFF(DAY,FAuditDate," + startDate + ")<=0");
            }
            if (endDate != "")
            {
                strSql.Append(" and DATEDIFF(DAY,FAuditDate," + endDate + ")>=0 ");
            }
            strSql.Append(" ) AS d ");
            strSql.Append(" ON d.FItemNo=c.FItemNo");
            strSql.Append(" group by c.FEntityNo,c.FItemClsName,c.FItemClsNo,c.FItemName,c.FItemNo,c.FItemSpec) as c");
            strSql.Append(" on A.FItemNo=c.FItemNo) AS e");
            strSql.Append(" LEFT JOIN dbo.t_ItemCls");
            strSql.Append(" on e.FItemClsNo = dbo.t_ItemCls.FItemClsNo");
            if (itemClsNo == "00") 
            {
                itemClsNo = "";
            }

            if (itemClsNo != "") 
            {
                strSql.Append(" where dbo.t_ItemCls.FItemClsNo ='" + itemClsNo + "'");
            }

            strSql.Append(" order by dbo.t_ItemCls.FItemClsName asc ");


            return DbHelperSQL.Query(strSql.ToString());
        }

       

    }
}
